--SELECT * FROM lion_resumesearch
--DELETE FROM lion_resumesearch
INSERT INTO lion_resumesearch
SELECT 
case len(convert(varchar(10),id))
	when 6 then '00000000-0000-0000-0000-000000' + convert(varchar(10),id) 
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),id) 
    when 4 then '00000000-0000-0000-0000-00000000' + convert(varchar(10),id) 
    when 3 then '00000000-0000-0000-0000-000000000' + convert(varchar(10),id) 
    when 2 then '00000000-0000-0000-0000-0000000000' + convert(varchar(10),id) 
    when 1 then '00000000-0000-0000-0000-00000000000'+ convert(varchar(10),id) 
    end As Id,SearcherName AS [Name],Case ResumeUpdatedDate when 0 then null else  ResumeUpdatedDate end AS [ResumePostedDuration],case right(wantworkarea,1) when '0' then null else right(wantworkarea,7) end AS [JobLacation],case AgeMin when 0 then null else agemin end AS [MinAge],case AgeMax when 0 then null else agemax end AS [MaxAge],case sexcommand  when -1 then null else sexcommand end AS [Gender],case WorkExperience when 0 then null else workexperience end AS [YearsOfExperience],case left(educational,1) when '0' then null when '1' then LEFT(educational,6)end AS [EducationLevel],NULL AS [SchoolTag],NULL AS [CareerLevels],case salarycomm when 0 then null else salarycomm end  AS [SalaryRange],case left(wantlanguage,1) when '0' then null else left(wantlanguage,6) end  AS [Language],112103 AS [Level],NULL AS [JobType],case wantindustry when 0 then null else wantindustry end AS [Industry],case right(currentjobclass,1) when '0' then null else right(currentjobclass,9) end AS [Category],case WantCompanyChar when 0 then null else wantcompanychar end AS [CompanyType],case right(liveaddress,1) when '0' then null else right(liveaddress,7) end AS [Residence],NULL AS [PostalCode],case right(accountaddress,1) when '0' then null else right(accountaddress,7) end AS [Household],case searcherTypewhen 122101 then 1else 0 end AS [IsSubscribed],OrderWebJob AS [Frequency],Resumecount AS [Deliveries],Receivemail AS [SubscribedEmail],updateddate AS [LastSearched],case len(convert(varchar(10),companyid))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),companyid) 
    when 4 then '00000000-0000-0000-0000-00000000' + convert(varchar(10),companyid) 
    when 3 then '00000000-0000-0000-0000-000000000' + convert(varchar(10),companyid) 
    when 2 then '00000000-0000-0000-0000-0000000000' + convert(varchar(10),companyid) 
    when 1 then '00000000-0000-0000-0000-00000000000'+ convert(varchar(10),companyid) 
    end AS [CompanyId],Createddate AS [DateCreated],updateddate AS [LastUpdated],case len(convert(varchar(10),companyid))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),companyid) 
    when 4 then '00000000-0000-0000-0000-00000000' + convert(varchar(10),companyid) 
    when 3 then '00000000-0000-0000-0000-000000000' + convert(varchar(10),companyid) 
    when 2 then '00000000-0000-0000-0000-0000000000' + convert(varchar(10),companyid) 
    when 1 then '00000000-0000-0000-0000-00000000000'+ convert(varchar(10),companyid) 
    end AS [ModifiedBy]FROM srv_lnk.nchr2008.dbo.ComSearcher

-- SELECT TOP 100 * FROM srv_lnk.nchr2008.dbo.ComSearcher
